# -*- coding:utf-8 -*-

from win32com.client import DispatchEx
import pythoncom
from PIL import ImageGrab
# import uuid

# 操作Excel
class cmd_excel(object):
    '''
    实现打开Excel，截图保存，关闭
    '''
    def __init__(self,filename):
        pythoncom.CoInitialize()
        self.full_name = filename
        self.excel = DispatchEx("Excel.Application")  # 启动excel
        self.excel.Visible = True  # 可视化
        self.excel.DisplayAlerts = False  # 是否显示警告
        self.wb = self.excel.Workbooks.Open(filename)  # 打开excel
        # self.wb.RefreshAll()

    # 关闭保存并关闭Excel
    def close_excel(self):
        self.wb.Close(SaveChanges=True)
        self.excel.Quit()
        pythoncom.CoInitialize()


    # 刷新Excel
    def refresh_excel(self,sheet_name,PivotTable):
        self.wb.Worksheets(sheet_name).PivotTables(PivotTable).PivotCache().Refresh()


    # 对日期进行筛选
    def filter_date(self,sheet_name,PivotTable,new_date,PivotFields = '日期',old_date = None):
        self.wb.Worksheets(sheet_name).PivotTables(PivotTable).PivotFields(PivotFields).PivotItems(new_date).Visible = True
        if old_date is None:
            pass
        else:
            self.wb.Worksheets(sheet_name).PivotTables(PivotTable).PivotFields(PivotFields).PivotItems(old_date).Visible = False


    # 抓取Excel指定sheet的指定单元格转化为图片保存在本地
    def excel_catch_screen(self,sheet_name, screen_area, shape = "Picture 1", paste_range = "BT10",save_name=None):
        # try:
        print(sheet_name)
        worksheet = self.wb.Sheets(sheet_name)
        print(screen_area)
        print("进行截图")
        worksheet.Range(screen_area).CopyPicture()
        worksheet.Paste(worksheet.Range(paste_range))  # 粘贴 ws.Paste(ws.Range('B1'))  # 将图片移动到具体位置
        print("已粘贴完成")
        # name = str(uuid.uuid4())  # 重命名唯一值
        # new_shape_name = name[:6]
        # self.excel.Selection.ShapeRange.Name = new_shape_name  # 将刚刚选择的Shape重命名，避免与已有图片混淆
        # worksheet.Shapes(new_shape_name).Copy()
        worksheet.Shapes(shape).Copy()
        worksheet.Shapes(shape).Copy()
        img = ImageGrab.grabclipboard()  # 获取剪贴板的图片数据
        # if img is None:
        #     print('Error1')
        # else:
        print("保存图片中...")
        img.save(save_name)
        print("删除图片")
        worksheet.Shapes(shape).Delete()
        # worksheet.Shapes(new_shape_name).Delete()
        # except Exception as e:
        #     print('Error2: %s' % str(e))
